Schema | ${Schema_Default} |
Table Name | eth mdm21 organization |
S3 URL Location | ${MDM21_S3_Target_Folder} |
S3 Object Prefix | ETH MDM21 Organization.txt |
IAM Role Arn | arn:aws:iam::<aws-account-id>:role/<role-name> |
Generate Manifest | No |
Data File Type | Delimited |
Delimiter | \t |
Compress Data | No |
Null As | |
Escape | No |
Allow Overwrites | Yes |
Parallel | No |
Add Quotes | No |
Max File Size (MB) | |
Include Header | No |
Encryption | None |
Source Type | S3 |
Source URL | s3://ethicon/outbound/Outbound_Process/MDM21/ETH MDM21 Organization.txt000 |
Unpack ZIP file | No |
Target Type | SFTP |
Gzip data | No |
Target Object Name | ETH MDM21 Organization.txt |
Set Home Directory as Root | No |
Target URL | https://smft.axtria.com/MDM21 |
Target Username | JnJ_Ethicon |
Target Password | ******** |
Target SFTP Key |
Main Table | Geo Hier: Rename |
Main Table Alias | filter |
Joins | outbound_dest_fiscal_calendar, fiscal, Left |
Join Expressions | "filter"."efftv_start_dt"="fiscal"."efftv_start_dt", filter_Left_fiscal |
Output Columns | filter.lower_algn_struc_cd, lower_algn_struc_cd, filter.lower_geo_id, lower_geo_id, filter.upper_algn_struc_cd, upper_algn_struc_cd, filter.upper_geo_id, upper_geo_id, filter.efftv_start_dt, filter_efftv_start_dt, filter.efftv_end_dt, filter_efftv_end_dt, filter.last_updated, last_updated, fiscal.fiscal_year_wk_start_dt, fiscal_year_wk_start_dt |
Main Table | Geo Hier Start Date |
Main Table Alias | start |
Joins | outbound_dest_fiscal_calendar, fiscal, Left |
Join Expressions | "start"."filter_efftv_end_dt"="fiscal"."efftv_end_dt", start_Left_fiscal |
Output Columns | start.lower_algn_struc_cd, lower_algn_struc_cd, start.lower_geo_id, lower_geo_id, start.upper_algn_struc_cd, upper_algn_struc_cd, start.upper_geo_id, upper_geo_id, start.filter_efftv_start_dt, filter_efftv_start_dt, start.filter_efftv_end_dt, filter_efftv_end_dt, start.last_updated, last_updated, start.fiscal_year_wk_start_dt, fiscal_year_wk_start_dt, fiscal.fiscal_year_wk_end_dt, fiscal_year_wk_end_dt |
Include Input Columns | Yes |
Calculations | CASE WHEN "fiscal_year_wk_start_dt" is NULL THEN '1900-01-01' ELSE "fiscal_year_wk_start_dt" END, EFFTV_START_DT_new, CASE WHEN "fiscal_year_wk_end_dt" is NULL THEN '9999-12-31' ELSE "fiscal_year_wk_end_dt" END, EFFTV_END_DT_new, DATE_PART_YEAR(to_date("last_updated",'YYYY-mm-dd')) || lpad(extract(month from(to_date("last_updated",'YYYY-mm-dd'))),2,'00') || lpad(extract(day from(to_date("last_updated",'YYYY-mm-dd'))),2,'00'), LAST_UPDATED_DT_new, len("lower_geo_id"), len_geo_id, "upper_algn_struc_cd"!="lower_algn_struc_cd", compare struc code |
Main Table | Geo Hier: Filter Exclude Upper Struc Not Equal Lower |
Main Table Alias | c1 |
Joins | level CD is territory, c2, Left |
Join Expressions | "c1"."upper_algn_struc_cd" = "c2"."upper_algn_struc_cd" and "c1"."efftv_start_dt_new" <= "c2"."efftv_end_dt_new" and "c1"."efftv_end_dt_new" >= "c2"."efftv_start_dt_new" , c1_Left_c2 |
Output Columns | c1.upper_geo_id, upper_geo_id, c1.lower_geo_id, lower_geo_id, c1.efftv_start_dt_new, efftv_start_dt, c1.efftv_end_dt_new, efftv_end_dt, c2.lower_algn_struc_cd, lower_algn_struc_cd, c2.efftv_start_dt_new, new_efftv_start_dt, c2.efftv_end_dt_new, new_efftv_end_dt |
Include Input Columns | Yes |
Calculations | case when "efftv_start_dt">"new_efftv_start_dt" then "efftv_start_dt" else "new_efftv_start_dt" end, EFFTV_START_DT_2, case when "efftv_end_dt">"new_efftv_end_dt" then "new_efftv_end_dt" else "efftv_end_dt" end, EFFTV_END_DT_2, case when len("lower_geo_id")=2 then "lower_algn_struc_cd" else "upper_geo_id" end, UPPER_GEO_ID_2 |
Schema | ${Schema_Default} |
Target Table Name | stg_mdm21_geo_hier_terr_n_upper |
Fix Data Type Mismatches | No |
Column Mapping | upper_geo_id, upper_geo_id, lower_geo_id, lower_geo_id, efftv_start_dt, efftv_start_dt, efftv_end_dt, efftv_end_dt, lower_algn_struc_cd, lower_algn_struc_cd, upper_algn_struc_cd, upper_algn_struc_cd |
Truncate | Truncate |
Automatic Compression | No |
Main Table | Geo_Id =3 digit |
Main Table Alias | c1 |
Joins | level CD is division, c2, Left |
Join Expressions | "c1"."upper_algn_struc_cd" = "c2"."upper_algn_struc_cd" and "c1"."lower_algn_struc_cd" = "c2"."lower_algn_struc_cd" and "c1"."efftv_start_dt_new" <= "c2"."efftv_end_dt_new" and "c1"."efftv_end_dt_new" >= "c2"."efftv_start_dt_new", c1_Left_c2 |
Output Columns | c1.upper_geo_id, upper_geo_id, c1.lower_geo_id, lower_geo_id, c1.efftv_start_dt_new, efftv_start_dt, c1.efftv_end_dt_new, efftv_end_dt, c2.lower_algn_struc_cd, lower_algn_struc_cd, c2.efftv_start_dt_new, new_efftv_start_dt, c2.efftv_end_dt_new, new_efftv_end_dt |
Main Table | Geo_Id =2 digit |
Main Table Alias | c1 |
Joins | level CD is division, c2, Left |
Join Expressions | "c1"."upper_algn_struc_cd"="c2"."upper_algn_struc_cd" and "c1"."efftv_start_dt_new"<="c2"."efftv_end_dt_new" and "c1"."efftv_end_dt_new">="c2"."efftv_start_dt_new", c1_Left_c2 |
Output Columns | c1.upper_geo_id, upper_geo_id, c1.lower_geo_id, lower_geo_id, c1.efftv_start_dt_new, efftv_start_dt, c1.efftv_end_dt_new, efftv_end_dt, c2.lower_algn_struc_cd, lower_algn_struc_cd, c2.efftv_start_dt_new, new_efftv_start_dt, c2.efftv_end_dt_new, new_efftv_end_dt |
Main Table | Geos: Rename Cols |
Main Table Alias | filter |
Joins | outbound_dest_fiscal_calendar, fiscal, Left |
Join Expressions | cast("filter"."efftv_start_dt" as date)= cast("fiscal"."efftv_start_dt" as date), filter_Left_fiscal |
Output Columns | filter.algn_struc_cd, algn_struc_cd, filter.geo_id, geo_id, filter.geo_nm, geo_nm, filter.level_cd, level_cd, filter.efftv_start_dt, filter_efftv_start_dt, filter.efftv_end_dt, filter_efftv_end_dt, filter.last_updated, last_updated, fiscal.fiscal_year_wk_start_dt, fiscal_year_wk_start_dt |
Main Table | Get Start Date |
Main Table Alias | start |
Joins | outbound_dest_fiscal_calendar, fiscal, Left |
Join Expressions | cast("start"."filter_efftv_end_dt" as date) = cast("fiscal"."efftv_end_dt" as date), start_Left_fiscal |
Output Columns | start.algn_struc_cd, algn_struc_cd, start.geo_id, geo_id, start.geo_nm, geo_nm, start.level_cd, level_cd, start.filter_efftv_start_dt, filter_efftv_start_dt, start.filter_efftv_end_dt, filter_efftv_end_dt, start.last_updated, last_updated, start.fiscal_year_wk_start_dt, fiscal_year_wk_start_dt, fiscal.fiscal_year_wk_end_dt, fiscal_year_wk_end_dt |
Include Input Columns | Yes |
Calculations | CASE WHEN "fiscal_year_wk_start_dt" is NULL THEN '1900-01-01' ELSE "fiscal_year_wk_start_dt" END, EFFTV_START_DT_new, CASE WHEN "fiscal_year_wk_end_dt" is NULL THEN '9999-12-31' ELSE "fiscal_year_wk_end_dt" END, EFFTV_END_DT_new, DATE_PART_YEAR(to_date("last_updated",'YYYY-mm-dd')) || lpad(extract(month from(to_date("last_updated",'YYYY-mm-dd'))),2,'00') || lpad(extract(day from(to_date("last_updated",'YYYY-mm-dd'))),2,'00'), LAST_UPDATED_DT_new, len("geo_id"), geo_id_length |
Main Table | GEO_ID is not like ???? |
Main Table Alias | c1 |
Joins | level CD is territory, c2, Left |
Join Expressions | "c1"."algn_struc_cd"="c2"."upper_algn_struc_cd" and "c1"."efftv_start_dt_new"<="c2"."efftv_end_dt_new" and "c1"."efftv_end_dt_new">="c2"."efftv_start_dt_new" , c1_Left_c2 |
Output Columns | c1.algn_struc_cd, algn_struc_cd, c1.geo_id, geo_id, c1.geo_nm, geo_nm, c1.level_cd, level_cd, c1.efftv_start_dt_new, efftv_start_dt, c1.efftv_end_dt_new, efftv_end_dt, c1.last_updated_dt_new, last_updated_dt, c2.lower_algn_struc_cd, lower_algn_struc_cd, c2.efftv_start_dt_new, new_efftv_start_dt, c2.efftv_end_dt_new, new_efftv_end_dt |
Include Input Columns | Yes |
Calculations | case when "efftv_start_dt">"new_efftv_start_dt" then "efftv_start_dt" else "new_efftv_start_dt" end, EFFTV_START_DT_2, case when "efftv_end_dt">"new_efftv_end_dt" then "new_efftv_end_dt" else "efftv_end_dt" end, EFFTV_END_DT_2, case when "level_cd"='Nation' then "lower_algn_struc_cd" else "geo_id" end, GEO_ID_2 |
Schema | ${Schema_Default} |
Target Table Name | stg_mdm21_organization_geo_terr_n_mgr |
Fix Data Type Mismatches | No |
Column Mapping | algn_struc_cd, algn_struc_cd, geo_id, geo_id, level_cd, level_cd, geo_nm, geo_nm, efftv_start_dt, efftv_start_dt, efftv_end_dt, efftv_end_dt |
Truncate | Truncate |
Automatic Compression | No |
Main Table | Geo Id =2 digit |
Main Table Alias | c1 |
Joins | level CD is division, c2, Left |
Join Expressions | "c1"."algn_struc_cd" = "c2"."upper_algn_struc_cd" and "c1"."efftv_start_dt_new" <= "c2"."efftv_end_dt_new" and "c1"."efftv_end_dt_new" >= "c2"."efftv_start_dt_new" , c1_Left_c2 |
Output Columns | c1.algn_struc_cd, algn_struc_cd, c1.geo_id, geo_id, c1.geo_nm, geo_nm, c1.level_cd, level_cd, c1.efftv_start_dt_new, efftv_start_dt, c1.efftv_end_dt_new, efftv_end_dt, c1.last_updated_dt_new, last_updated_dt, c2.lower_algn_struc_cd, lower_algn_struc_cd, c2.efftv_start_dt_new, new_efftv_start_dt, c2.efftv_end_dt_new, new_efftv_end_dt |
Main Table | Parameter: Geo Hierarchy |
Main Table Alias | geo_hier |
Joins | outbound_dest_fiscal_calendar, fiscal, Left |
Join Expressions | cast("geo_hier"."efftv_start_dt" as date)= cast("fiscal"."efftv_start_dt" as date), geo_hier_Left_fiscal |
Output Columns | geo_hier.upper_algn_struc_cd, upper_algn_struc_cd, geo_hier.lower_algn_struc_cd, lower_algn_struc_cd, geo_hier.efftv_start_dt, geo_hier_efftv_start_dt, geo_hier.efftv_end_dt, geo_hier_efftv_end_dt, geo_hier.level_cd, level_cd, fiscal.fiscal_year_wk_start_dt, fiscal_year_wk_start_dt |
Main Table | Param Geo Hier Start |
Main Table Alias | geo_hier |
Joins | outbound_dest_fiscal_calendar, fiscal, Left |
Join Expressions | cast("geo_hier"."geo_hier_efftv_end_dt" as date)= cast("fiscal"."efftv_end_dt" as date), geo_hier_Left_fiscal |
Output Columns | geo_hier.upper_algn_struc_cd, upper_algn_struc_cd, geo_hier.lower_algn_struc_cd, lower_algn_struc_cd, geo_hier.geo_hier_efftv_start_dt, efftv_start_dt, geo_hier.geo_hier_efftv_end_dt, efftv_end_dt, geo_hier.level_cd, level_cd, geo_hier.fiscal_year_wk_start_dt, fiscal_year_wk_start_dt, fiscal.fiscal_year_wk_end_dt, fiscal_year_wk_end_dt |
Main Table | Combine Geos and Teams |
Main Table Alias | c1 |
Joins | outbound_src_mdm_lov_param, c2, Inner |
Join Expressions | "c1"."algn_struc_cd"="c2"."alignment structure code", c1_Inner_c2 |
Output Columns | c1.algn_struc_cd, algn_struc_cd, c1.geo_id, geo_id, c1.level_cd, level_cd, c1.geo_nm, geo_nm, c1.efftv_start_dt, efftv_start_dt, c1.efftv_end_dt, efftv_end_dt, c1.team_id, team_id, c2.org id prefix, org id prefix, c2.franchise code, franchise code, c2.sales org division code, sales org division code, c2.sales org name, sales org name |
Main Table | Get Organization ID (included recs) |
Main Table Alias | c1 |
Joins | included recs, c2, Left |
Join Expressions | "c1"."algn_struc_cd"="c2"."lower_algn_struc_cd" and "c1"."geo_id"="c2"."lower_geo_id" and "c1"."efftv_start_dt"<="c2"."efftv_end_dt" and "c1"."efftv_end_dt">="c2"."efftv_start_dt" , c1_Left_c2 |
Output Columns | c1.algn_struc_cd, algn_struc_cd, c1.geo_id, geo_id, c1.team_id, team_id, c1.level_cd, level_cd, c1.geo_nm, geo_nm, c1.efftv_start_dt, efftv_start_dt, c1.efftv_end_dt, efftv_end_dt, c1.org id prefix, org id prefix, c1.franchise code, franchise code, c1.sales org division code, sales org division code, c1.sales org name, sales org name, c2.upper_geo_id, upper_geo_id, c2.efftv_start_dt, new_efftv_start_dt, c2.efftv_end_dt, new_efftv_end_dt |
Include Input Columns | Yes |
Calculations | CASE WHEN "upper_geo_id" = "algn_struc_cd" THEN "geo_id" ELSE "upper_geo_id" END , upper_geo_id, 'OPCO_END', Provider ID, CURRENT_DATE, last_updated_dt, GREATEST (cast("efftv_start_dt" as date), cast("new_efftv_start_dt" as date)) , efftv_start_dt, LEAST (cast("efftv_end_dt" as date), cast("new_efftv_end_dt" as date)) , efftv_end_dt |
Main Table | Update Upper Terr & Constants |
Main Table Alias | c1 |
Joins | flag(included recs), c2, Inner |
Join Expressions | "c1"."algn_struc_cd"="c2"."algn_struc_cd" AND "c1"."upper_geo_id"="c2"."geo_id", c1_Inner_c2 |
Output Columns | c1.algn_struc_cd, algn_struc_cd, c1.geo_id, geo_id, c1.team_id, team_id, c1.level_cd, level_cd, c1.geo_nm, geo_nm, c1.efftv_start_dt, efftv_start_dt, c1.efftv_end_dt, efftv_end_dt, c1.org id prefix, org id prefix, c1.franchise code, franchise code, c1.sales org division code, sales org division code, c1.sales org name, sales org name, c1.upper_geo_id, upper_geo_id, c1.new_efftv_start_dt, new_efftv_start_dt, c1.new_efftv_end_dt, new_efftv_end_dt, c1.provider id, provider id, c1.last_updated_dt, last_updated_dt, c2.level_cd, upper_level_cd |
Main Table | Get Upper Territory Level |
Main Table Alias | gutl |
Joins | Filter Active Geographies, fag, Left |
Join Expressions | "gutl"."geo_id" = "fag"."geo_id", gutl_Left_fag |
Output Columns | gutl.algn_struc_cd, algn_struc_cd, gutl.geo_id, geo_id, gutl.team_id, team_id, gutl.level_cd, level_cd, gutl.geo_nm, geo_nm, gutl.efftv_start_dt, efftv_start_dt, gutl.efftv_end_dt, efftv_end_dt, gutl.org id prefix, org id prefix, gutl.franchise code, franchise code, gutl.sales org division code, sales org division code, gutl.sales org name, sales org name, gutl.upper_geo_id, upper_geo_id, gutl.new_efftv_start_dt, new_efftv_start_dt, gutl.new_efftv_end_dt, new_efftv_end_dt, gutl.provider id, provider id, gutl.last_updated_dt, last_updated_dt, gutl.upper_level_cd, upper_level_cd, fag.algn_struc_cd, fag_algn_struc_cd |
Main Table | Get Original Align Struct |
Main Table Alias | goas |
Joins | MDM LOV Parameter, lovp, Left |
Join Expressions | "goas"."fag_algn_struc_cd" = "lovp"."alignment structure code", goas_Left_lovp |
Output Columns | goas.algn_struc_cd, algn_struc_cd, goas.geo_id, geo_id, goas.team_id, team_id, goas.level_cd, level_cd, goas.geo_nm, geo_nm, goas.efftv_start_dt, efftv_start_dt, goas.efftv_end_dt, efftv_end_dt, goas.org id prefix, org id prefix, goas.franchise code, franchise code, goas.sales org division code, sales org division code, goas.sales org name, sales org name, goas.upper_geo_id, upper_geo_id, goas.new_efftv_start_dt, new_efftv_start_dt, goas.new_efftv_end_dt, new_efftv_end_dt, goas.provider id, provider id, goas.last_updated_dt, last_updated_dt, goas.upper_level_cd, upper_level_cd, lovp.sales org name, lovp_sales org name |
Include Input Columns | Yes |
Calculations | CASE WHEN "level_cd" ilike '%terr%' THEN "team_id" ELSE "org id prefix" || "team_id" END, geo_id, left("upper_level_cd",1), parent organization id, TRIM("geo_nm") , geo_nm, CASE WHEN left("level_cd",1) = 'S' THEN 'T' ELSE left("level_cd",1) END , organization id, CASE WHEN DATE_PART_YEAR(to_date("efftv_end_dt",'yyyy-mm-dd')) ='9999' THEN NULL ELSE "efftv_end_dt" END, efftv_end_dt, "org id prefix" || "upper_geo_id" , upper_geo_id |
Main Table | Update Final Constants |
Main Table Alias | ufc |
Joins | Agg for Oldest, afo, Inner |
Join Expressions | cast("ufc"."efftv_start_dt" as date) = cast("afo"."min_efftv_start_dt" as date) AND "ufc"."geo_id" = "afo"."geo_id" AND "ufc"."organization id" = "afo"."organization id" AND "ufc"."parent organization id" = "afo"."parent organization id" AND "ufc"."upper_geo_id" = "afo"."upper_geo_id", ufc_Inner_afo |
Output Columns | ufc.algn_struc_cd, algn_struc_cd, ufc.level_cd, level_cd, ufc.team_id, team_id, ufc.org id prefix, org id prefix, ufc.franchise code, franchise code, ufc.sales org division code, sales org division code, ufc.lovp_sales org name, sales org name, ufc.new_efftv_start_dt, new_efftv_start_dt, ufc.new_efftv_end_dt, new_efftv_end_dt, ufc.provider id, provider id, ufc.last_updated_dt, last_updated_dt, ufc.efftv_start_dt, efftv_start_dt, ufc.upper_level_cd, upper_level_cd, ufc.upper_geo_id, upper_geo_id, ufc.geo_id, geo_id, ufc.parent organization id, parent organization id, ufc.geo_nm, geo_nm, ufc.organization id, organization id, ufc.efftv_end_dt, efftv_end_dt |
Include Input Columns | Yes |
Calculations | DATE_PART_YEAR(to_date("efftv_start_dt",'YYYY-mm-dd')) || lpad(extract(month from(to_date("efftv_start_dt",'YYYY-mm-dd'))),2,'00') || lpad(extract(day from(to_date("efftv_start_dt",'YYYY-mm-dd'))),2,'00') , efftv_start_dt, DATE_PART_YEAR(to_date("efftv_end_dt",'YYYY-mm-dd')) || lpad(extract(month from(to_date("efftv_end_dt",'YYYY-mm-dd'))),2,'00') || lpad(extract(day from(to_date("efftv_end_dt",'YYYY-mm-dd'))),2,'00') , efftv_end_dt, DATE_PART_YEAR(to_date("last_updated_dt",'YYYY-mm-dd')) || lpad(extract(month from(to_date("last_updated_dt",'YYYY-mm-dd'))),2,'00') || lpad(extract(day from(to_date("last_updated_dt",'YYYY-mm-dd'))),2,'00') , last_updated_dt |
Column Mapping | provider id, Provider Id, geo_id, Organization ID, organization id, Organization Type Code, min_geo_nm, Organization Name, min_sales org division code, Sales Org Division Code, min_sales org name, Sales Org Team Code, min_franchise code, Franchise Code, upper_geo_id, Parent Organization ID, parent organization id, Parent Organization Type Code, efftv_start_dt, Organization Effective Date, efftv_end_dt, Organization End Date, last_updated_dt, Last Update Date |
Main Table | Cust Team Geo (Included recs) |
Main Table Alias | c1 |
Joins | Exclude rolliing to 80, c2, Inner |
Join Expressions | "c1"."algn_struc_cd"="c2"."algn_struc_cd" and "c1"."geo_id"="c2"."geo_id", c1_Inner_c2 |
Output Columns | c1.algn_struc_cd, algn_struc_cd, c1.geo_id, geo_id, c1.team_id, team_id, c1.split_pct, split_pct, c1.efftv_start_dt, efftv_start_dt, c1.efftv_end_dt, efftv_end_dt, c1.last_updated_dt, last_updated_dt |
Main Table | Get Valid Teams |
Main Table Alias | c1 |
Joins | Agg for Largest Team Record, c2, Inner |
Join Expressions | "c1"."algn_struc_cd"="c2"."algn_struc_cd" and "c1"."split_pct"="c2"."max_split_pct" and "c1"."team_id"="c2"."team_id", c1_Inner_c2 |
Output Columns | c1.algn_struc_cd, algn_struc_cd, c1.geo_id, geo_id, c1.team_id, team_id, c1.split_pct, split_pct, c1.efftv_start_dt, efftv_start_dt, c1.efftv_end_dt, efftv_end_dt, c1.last_updated_dt, last_updated_dt |
Main Table | One Record Per Team |
Main Table Alias | c1 |
Joins | Exclude rolliing to 80, c2, Inner |
Join Expressions | "c1"."algn_struc_cd"="c2"."algn_struc_cd" and "c1"."max_geo_id"="c2"."geo_id", c1_Inner_c2 |
Output Columns | c1.algn_struc_cd, algn_struc_cd, c1.max_geo_id, geo_id, c2.level_cd, level_cd, c2.geo_nm, geo_nm, c2.efftv_start_dt, efftv_start_dt, c2.efftv_end_dt, efftv_end_dt, c1.team_id, team_id |
Main Table | flag(included recs) |
Main Table Alias | c1 |
Joins | Combine Rolling to 80, c2, Left |
Join Expressions | "c1"."algn_struc_cd" = "c2"."lower_algn_struc_cd" and "c1"."geo_id" = "c2"."lower_geo_id", c1_Left_c2 |
Output Columns | c1.algn_struc_cd, algn_struc_cd, c1.geo_id, geo_id, c1.level_cd, level_cd, c1.geo_nm, geo_nm, c1.efftv_start_dt, efftv_start_dt, c1.efftv_end_dt, efftv_end_dt, c1.geo_id, team_id, c2.lower_algn_struc_cd, lower_right |
Main Table | Get Territories Rolling to 80: Filter Yes |
Main Table Alias | c1 |
Joins | Geo Hier Merge Terr and Upper (included recs), c2, Inner |
Join Expressions | "c1"."upper_algn_struc_cd"= "c2"."lower_algn_struc_cd" and "c1"."upper_geo_id"="c2"."lower_geo_id" , c1_Inner_c2 |
Output Columns | c1.lower_algn_struc_cd, c1_lower_algn_struc_cd, c1.lower_geo_id, c1_lower_geo_id, c1.upper_algn_struc_cd, c1_upper_algn_struc_cd, c1.upper_geo_id, c1_upper_geo_id, c1.efftv_start_dt, c1_efftv_start_dt, c1.efftv_end_dt, c1_efftv_end_dt, c1.flag, c1_flag, c2.lower_algn_struc_cd, c2_lower_algn_struc_cd, c2.lower_geo_id, c2_lower_geo_id, c2.upper_algn_struc_cd, c2_upper_algn_struc_cd, c2.upper_geo_id, c2_upper_geo_id, c2.efftv_start_dt, c2_efftv_start_dt, c2.efftv_end_dt, c2_efftv_end_dt, c2.flag, c2_flag |
Script | import datetime x = datetime.datetime.now() x=str(x).split('.') x=x[0] print('time_stamp :'+str(x)) print('Archive_temp_current_table :'+str(Archive_temp_current_table)) print('Archive_temp_current_file :'+str(Archive_temp_current_file)) Archive_temp_current_file=Archive_temp_current_file+'_'+x+'__' print('Archive_temp_current_file upt :'+str(Archive_temp_current_file)) context.updateVariable('Archive_temp_current_file', str(Archive_temp_current_file)) print('Archive_type :'+Archive_type) #dynamically change path for Archive if Archive_type=='ONEMD': S3_temp_Archive_Location=S3_ONEMD_Archive_Location print('Archive Locations :'+str(S3_temp_Archive_Location)) elif Archive_type=='MDM21': S3_temp_Archive_Location=S3_MDM21_Archive_Location print('Archive Locations :'+str(S3_temp_Archive_Location)) else: S3_temp_Archive_Location=S3_EUSS_Archive_Location print('Archive Locations :'+str(S3_temp_Archive_Location)) |
Interpreter | Jython |
Schema | ${Schema_Default} |
Table Name | ${Archive_temp_current_table} |
S3 URL Location | ${S3_ONEMD_Archive_Location} |
S3 Object Prefix | ${Archive_temp_current_file} |
IAM Role Arn | arn:aws:iam::775229046089:role/RedshiftS3Athna |
Generate Manifest | No |
Data File Type | Delimited |
Delimiter | | |
Compress Data | Yes |
Compression Type | GZIP |
Null As | |
Escape | No |
Allow Overwrites | Yes |
Parallel | No |
Add Quotes | No |
Max File Size (MB) | |
Include Header | No |
Encryption | None |
Schema | ${Schema_Default} |
Table Name | ${Archive_temp_current_table} |
S3 URL Location | ${S3_MDM21_Archive_Location} |
S3 Object Prefix | ${Archive_temp_current_file} |
IAM Role Arn | arn:aws:iam::775229046089:role/RedshiftS3Athna |
Generate Manifest | No |
Data File Type | Delimited |
Delimiter | | |
Compress Data | Yes |
Compression Type | GZIP |
Null As | |
Escape | No |
Allow Overwrites | Yes |
Parallel | No |
Add Quotes | No |
Max File Size (MB) | |
Include Header | No |
Encryption | None |
Schema | ${Schema_Default} |
Table Name | ${Archive_temp_current_table} |
S3 URL Location | ${S3_EUSS_Archive_Location} |
S3 Object Prefix | ${Archive_temp_current_file} |
IAM Role Arn | arn:aws:iam::775229046089:role/RedshiftS3Athna |
Generate Manifest | No |
Data File Type | Delimited |
Delimiter | | |
Compress Data | Yes |
Compression Type | GZIP |
Null As | |
Escape | No |
Allow Overwrites | Yes |
Parallel | No |
Add Quotes | No |
Max File Size (MB) | |
Include Header | No |
Encryption | None |